﻿// Copyright (c) Kona Framework Team. All rights reserved.
// For continued development and updates, visit http://www.codeplex.com/kona
// For a copy of the license please vist: http://www.codeplex.com/kona/license
using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
using Kona.Data;

namespace Kanaka.ResourceAccess.Reports.Queries
{
    internal class UsCustomerCountsByStateForFiscalYearQuery
    {
        private static readonly XNamespace XMLA_NAMESPACE = "urn:schemas-microsoft-com:xml-analysis:mddataset";


        internal AdoMdConnection Connection { get; private set; }
        internal Parameters.FiscalYear FiscalYear { get; private set; }
        internal MdxStatements.UsCustomerCountsByStateForFiscalYearMdx Mdx;

        internal UsCustomerCountsByStateForFiscalYearQuery(AdoMdConnection connection, Parameters.FiscalYear fiscalYear)
        {
            this.FiscalYear = fiscalYear;
            this.Connection = connection;
            this.Mdx = new MdxStatements.UsCustomerCountsByStateForFiscalYearMdx(this.FiscalYear);
        }

        internal IEnumerable<StateProvinceMonthlyCustomerCounts> Execute()
        {
            using (AdoMdCommand Command = new AdoMdCommand())
            {
                Command.Connection = this.Connection;
                this.Connection.Open();
                Command.CommandText = this.Mdx.ToString();
                XmlReader Reader = Command.ExecuteXmlReader();
                XElement ResultsElement = Transform(Reader);

                return Transform(ResultsElement);
            }
        }

        private XElement Transform(XmlReader reader)
        {
            XNamespace ns = "urn:schemas-microsoft-com:xml-analysis:mddataset";
            reader.ReadToDescendant("Axes", ns.NamespaceName);
            System.Xml.XmlReader SubTreeReader = reader.ReadSubtree();
            XElement AxesElement = XElement.Load(SubTreeReader);
            reader.ReadToFollowing("CellData", ns.NamespaceName);
            SubTreeReader = reader.ReadSubtree();
            XElement CellDataElement = XElement.Load(SubTreeReader);
            return new XElement(ns + "root", AxesElement, CellDataElement);
        }

        private static List<StateProvinceMonthlyCustomerCounts> Transform(XElement rootElement)
        {
            #region Parameter Validation
            if (rootElement == null)
            { return null; }

            if (rootElement.Element(XMLA_NAMESPACE + "Axes") == null)
            { return null; }

            XElement Axis0Element = (from e in rootElement.Descendants(XMLA_NAMESPACE + "Axis")
                                     where e.Attribute("name").Value == "Axis0"
                                     select e).Single();
            if (Axis0Element == null)
            { return null; }

            XElement Axis1Element = (from e in rootElement.Descendants(XMLA_NAMESPACE + "Axis")
                                     where e.Attribute("name").Value == "Axis1"
                                     select e).Single();
            if (Axis1Element == null)
            { return null; }

            XElement CellDataElement = rootElement.Element(XMLA_NAMESPACE + "CellData");
            if (CellDataElement == null)
            { return null; }
            #endregion

            var Axis1MemberElements = Axis1Element.Descendants(XMLA_NAMESPACE + "Member");
            var Axis0MemberElements = Axis0Element.Descendants(XMLA_NAMESPACE + "Member");
            var AllMonthlyCustomerCounts = new List<MonthlyCustomerCount>();
            var StateProvinceMonthlyCountsList = new List<StateProvinceMonthlyCustomerCounts>();

            foreach (var RowPrototype in Axis1MemberElements)
            {
                var RowMemberUNameElement = RowPrototype.Element(XMLA_NAMESPACE + "UName");
                var RowMemberCaptionElement = RowPrototype.Element(XMLA_NAMESPACE + "Caption");
                var NewStateProvinceMonthlyCounts = new StateProvinceMonthlyCustomerCounts
                {
                    StateProvince = new StateProvince
                    {
                        Caption = RowMemberCaptionElement.Value,
                        UName = RowMemberUNameElement.Value
                    },
                    MonthlyCustomerCounts = new List<MonthlyCustomerCount>()
                };


                foreach (var ColumnPrototype in Axis0MemberElements)
                {
                    var ColumnUNameElement = ColumnPrototype.Element(XMLA_NAMESPACE + "UName");
                    var ColumnCaptionElement = ColumnPrototype.Element(XMLA_NAMESPACE + "Caption");
                    var NewMonthlyCustomerCount = new MonthlyCustomerCount
                    {
                        MonthOfYear = new MonthofYear
                        {
                            Caption = ColumnCaptionElement.Value,
                            UName = ColumnUNameElement.Value
                        }
                    };
                    AllMonthlyCustomerCounts.Add(NewMonthlyCustomerCount);
                    NewStateProvinceMonthlyCounts.MonthlyCustomerCounts.Add(NewMonthlyCustomerCount);
                }
                StateProvinceMonthlyCountsList.Add(NewStateProvinceMonthlyCounts);
            }

            foreach (var CellElement in CellDataElement.Elements(XMLA_NAMESPACE + "Cell"))
            {
                int CellOrdinalValue = Convert.ToInt32(CellElement.Attribute("CellOrdinal").Value);
                var CellValueElement = CellElement.Element(XMLA_NAMESPACE + "Value");
                int CellValue = Convert.ToInt32(CellValueElement.Value);
                var SelectedMonthlyCustomerCount = AllMonthlyCustomerCounts[CellOrdinalValue];
                SelectedMonthlyCustomerCount.Count = CellValue;
            }
            return StateProvinceMonthlyCountsList;
        }
    }
}

